﻿/*
 * =====================================================================================
 *
 *       Filename:  AccessHelper.cs
 *
 *    Description:  access tools
 *
 *        Version:  1.1
 *        Created:  
 *       Revision:  none
 *       Compiler:  
 *
 *         Author:  imbedinlove
 *        Company:  
 *           Date:  2013年5月23日 16:20:56
 *
 * =====================================================================================
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Collections;

namespace ImbedLibrary
{
    public class AccessHelper:SQLBaseHelper
    {
        //public delegate void IDataRender<T>(IDataReader reader, IList<T> list);
        //private readonly string connString = ConfigurationManager.ConnectionStrings["AccessCon"].ConnectionString;

        /// <summary>
        /// Helper
        /// </summary>
        AccessHelper()
        {
            String basePath = AppDomain.CurrentDomain.BaseDirectory;
            connString = string.Format(connString, basePath);
        }

        /// <summary>
        /// Helper
        /// </summary>
        /// <param name="basePath">basePath</param>
        public AccessHelper(String basePath):base( basePath)
        {
            if (string.IsNullOrEmpty(basePath))
            {
                throw new ArgumentException("E07001:数据库路径不能为空");
            }
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + basePath + ";Persist Security Info=True";
        }

        /// <summary>
        /// CreateCommand
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="conn">conn</param>
        /// <param name="param">param</param>
        /// <returns>OleDbCommand</returns>
        private OleDbCommand CreateCommand(string sql, OleDbConnection conn, params OleDbParameter[] param)
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.Parameters.AddRange(param);
            conn.Open();
            return cmd;
        }

        /// <summary>
        /// CreateCommand
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="conn">conn</param>
        /// <returns>OleDbCommand</returns>
        private OleDbCommand CreateCommand(string sql, OleDbConnection conn)
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            conn.Open();
            return cmd;
        }

        #region ExcuteScalar
        /// <summary>
        /// ExcuteScalar
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="param">param</param>
        /// <returns>count</returns>
        public override int ExcuteScalar(string sql, params IDataParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                int count = 0;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn, IDataParameterToOleDbParameter(param));
                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }
        #endregion

        #region ExcuteScalar
        /// <summary>
        /// ExcuteScalar
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns>count</returns>
        public int ExcuteScalar(string sql)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                int count = 0;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn);
                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="param">param</param>
        /// <returns>count</returns>
        public int ExecuteNonQuery(string sql, params OleDbParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                int count = 0;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn, param);
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns>count</returns>
        public override int ExecuteNonQuery(string sql)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                int count = 0;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn);
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string msg = e.Message;
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <typeparam name="T">class</typeparam>
        /// <param name="sql">sql</param>
        /// <param name="render">render</param>
        /// <param name="param">param</param>
        /// <returns>List 'T'</returns>
        public override IList<T> ExecuteReader<T>(string sql, IDataRender<T> render, params IDataParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                IList<T> list = new List<T>();
                OleDbDataReader reader = null;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn, IDataParameterToOleDbParameter(param));
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    render( list, reader);
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <typeparam name="T">T</typeparam>
        /// <param name="sql">sql</param>
        /// <param name="render">render</param>
        /// <returns>list T</returns>
        public override IList<T> ExecuteReader<T>(string sql, IDataRender<T> render)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                IList<T> list = new List<T>();
                OleDbDataReader reader = null;
                try
                {
                    OleDbCommand cmd = CreateCommand(sql, conn);
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    render(list, reader);
                }
                catch (Exception)
                {
                    Console.WriteLine(connString);
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }

        /// <summary>
        /// 执行sql语句返回数据
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns>DataSet</returns>
        public override DataSet ExecuteReader(string sql)
        {
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection (connString))
            {
                try
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                    adapter.Fill(ds);
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds;
           
        }
        #endregion

        #region GetTableField
        /// <summary>
        /// Get table filed
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <param name="type">COLUMN_NAME or DESCRIPTION</param>
        /// <returns></returns>
        public override List<string> GetTableField(string tableName, string type)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                DataTable dt = new DataTable();
                DataView dv = null;
                List<string> list = new List<string>();
                try
                {
                    conn.Open();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
                    dv = new DataView(dt);
                    dv.Sort = "ORDINAL_POSITION ASC";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dv[i]["DESCRIPTION"].ToString().IndexOf("fk") == -1)
                            list.Add(dv[i][type].ToString());
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }
        #endregion

        #region IDataParameter to OleDbParameter
        /// <summary>
        /// 转换IDataParameter[] 为 OleDbParameter[]
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        protected OleDbParameter[] IDataParameterToOleDbParameter(params IDataParameter[] param)
        {
            OleDbParameter[] oleParam = new OleDbParameter[param.Length];
            for (int i = 0; i <param.Length ; i++)
            {
                oleParam[i] = param[i] as OleDbParameter;
            }
            return oleParam;
        }
        #endregion
    }
}
